# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.

import tkinter as tk
from tkinter import ttk,Text
import webbrowser
import os
import re

class Datepicker:
    def __init__(self, parent):
        self.parent = parent
        self.start()

    def start(self):#启用，创建两个页签
        notebook = ttk.Notebook(root)
        notebook.pack(expand=True, fill="both")

        # > 标签1
        frame1 = ttk.Frame(notebook)
        # > 标签2
        frame2 = ttk.Frame(notebook)

        notebook.add(frame1, text="文件管理")
        notebook.add(frame2, text="SQL脚本识别表清单")

        self.file_manger(frame1) #文件管理
        self.table_finer(frame2) #SQL脚本识别表清单

    def file_manger(self, frame1):
        listbox = tk.Listbox(frame1, width=200)
        listbox.grid()
        # 设置说明
        label123 = ttk.Label(listbox, text="""点击"文件路径"打开文件""", background="yellow", relief="solid",
                             padding=(5, 5), width=68, font=("微软雅黑", 10, "bold"))
        label123.grid(row=0, column=0, columnspan=3)

        # 设置标题
        label = ttk.Label(listbox, text="序号", background="blue", relief="solid", foreground='white', padding=(5, 5),
                          width=4, font=("微软雅黑", 10, "bold"))
        label.grid(row=1, column=0)
        label = ttk.Label(listbox, text="文件名", background="blue", relief="solid", foreground='white', padding=(5, 5),
                          width=20, font=("微软雅黑", 10, "bold"))
        label.grid(row=1, column=1)
        label = ttk.Label(listbox, text="文件路径", background="blue", relief="solid", foreground='white',
                          padding=(5, 5), width=40, font=("微软雅黑", 10, "bold"))
        label.grid(row=1, column=2)
        #20250717 指定打开文档的格式，解决中文路径出错问题
        with open('fileManger.txt', 'r', encoding="utf-8") as file:
            line = file.readline()
            rowNum = 1  # 行号控制
            while line:
                rowNum = rowNum + 1
                l1 = line.split()[0]
                l2 = line.split()[1]
                l3 = line.split()[2]
                # listbox.insert(tk.END, l2, urllib=l3)
                label = ttk.Label(listbox, text=l1, background="white", relief="solid", padding=(5, 5), width=4,
                                  font=("微软雅黑", 10))
                label.grid(row=rowNum, column=0)
                label = ttk.Label(listbox, text=l2, background="white", relief="solid", padding=(5, 5), width=20,
                                  font=("微软雅黑", 10))
                label.grid(row=rowNum, column=1)

                label1 = ttk.Label(listbox, text=l3, background="white", relief="solid", padding=(5, 5), width=40,
                                   cursor="hand2", font=("微软雅黑", 10))
                label1.bind("<Button-1>", self.open_file)
                label1.grid(row=rowNum, column=2)
                line = file.readline()
    def open_file(self, event):
        url = event.widget.cget("text")
        file_path = url  #.replace("file://", "")
        if os.path.exists(file_path):
            os.startfile(file_path)
        else:
            webbrowser.open(url)

    def table_finer(self, frame2): # 控件的摆放，关键在增加画布的控制。利用画布布局上下，在画布里面再控制左右。
        # 创建画布，用于摆放“输入框”和对应滚动条
        canvas = tk.Canvas(frame2, width=500, height=23)
        canvas.pack()
        # 创建输入框
        self.input_text = Text(canvas, name='输入框', width=500, height=23)
        # 创建垂直滚动条
        scrollbar_y = tk.Scrollbar(canvas, command=self.input_text.yview)
        scrollbar_y.pack(side=tk.RIGHT, anchor=tk.NE, fill=tk.Y)
        self.input_text.pack(side=tk.RIGHT)
        # 将滚动条与Text组件关联
        self.input_text.config(yscrollcommand=scrollbar_y.set)

        # 创建点击按钮
        button = ttk.Button(frame2, text='开始识别', command=self.finder_scan)
        button.pack()

        # 创建画布2，用于摆放“输出框”和对应滚动条
        canvas2 = tk.Canvas(frame2, width=500, height=23)
        canvas2.pack()
        # 创建输出框
        self.output_text = Text(canvas2, name='输出框', width=600)
        # 创建垂直滚动条
        scrollbar_y2 = tk.Scrollbar(canvas2, command=self.output_text.yview)
        scrollbar_y2.pack(side=tk.RIGHT, fill=tk.Y)
        self.output_text.pack(side=tk.RIGHT)
        # 将滚动条与Text组件关联
        self.output_text.config(yscrollcommand=scrollbar_y2.set)

    def finder_scan(self):
        self.output_text.delete(1.0, tk.END) # 把内容输出到输出框前先清除旧内容
        sql = self.input_text.get("1.0", tk.END).strip() # 获取输入框内容
        tables = self.find_tables(sql)
        new_tables = sorted(list(set(tables))) #去重，排序
        # 换行输出识别到的表名
        for i in new_tables:
            self.output_text.insert(tk.END, i+'\n') # 加上'\n'，表示换行输出

    def find_tables(self, sql):
        # 正则表达式：\s+ 匹配一个或多个空白字符
        # (\w+) 表示匹配一个或多个字母、数字或下划线的单词
        # 具体来说，\b 表示单词边界，FROM 或 JOIN 表示要匹配的字符串之一，\s+ 表示一个或多个空白字符，(\w+\.\w+) 表示捕获一个由字母、数字和下划线组成的单词，再跟着一个点号（.），最后是另一个由字母、数字和下划线组成的单词。
        pattern = r'\bFROM\s+(\w+\.\w+)|\bJOIN\s+(\w+\.\w+)'
        tables = re.findall(pattern, sql, re.IGNORECASE)
        table_names = [table for sublist in tables for table in sublist if table]
        return table_names

if __name__ == '__main__':
    root = tk.Tk()
    root.title("小小工具箱")
    root.geometry("600x600")
    Datepicker(root)
    root.mainloop()

